<?php

/**
 * @author @author Samrat Khan & Sadik Sarfaraz - Apr 11, 2013
 */
/**
 * 
 * selectAll
 * 
 * @param string $orderby You can order data using Ascending/Descending, use ASC or DESC onto this parameter
 * @param string $orderbycolumn Orderby Column Name e.g. employeename
 * @param string $tblname Table Name
 * @param string $db Common database connection string parameter
 * @return string $rows Returning the rows of a table
 * 
 */
function selectAll($orderby, $orderbycolumn, $tblname, $db) {
    $query = 'SELECT * FROM '.$tblname.' ORDER BY '.$orderbycolumn.' '.$orderby.'';
    
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    return $rows;
}

/**
 * @param string $tblname Table Name
 * @param string $orderby You can order data using Ascending/Descending, use ASC or DESC onto this parameter
 * @param string $orderbycolumn Orderby Column Name e.g. employeename
 * @param string $whereCol where column name e.g. employee_id, generally primary key selector
 * @param numeric $whereID where id to select a row
 * @param numeric $offset the offset of the limit of a query
 * @param numeric $limit the limit of the query
 * @param string $db Common database connection string parameter
 * @return string $rows Returning the rows of a table
 */
function selectAllWhere($tblname, $orderby, $orderbycolumn, $whereCol, $whereID, $offset, $limit, $db) {
    $query = 'SELECT * FROM '.$tblname.' WHERE '.$whereCol.' = "'.$whereID.'" ORDER BY '.$orderbycolumn.' '.$orderby.' LIMIT '.$offset.', '.$limit.' ';
    //LIMIT '.$offset.', '.$limit.'
    //var_dump($query);
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    return $rows;
}
/**
 * 
 * @param string $tblname Table Name
 * @param string $orderby You can order data using Ascending/Descending, use ASC or DESC onto this parameter
 * @param string $betCol Date Between based on column name
 * @param date $from Pick From date with following format e.g 2011-05-11
 * @param string $to Pick To date with following format e.g 2013-05-11
 * @param string $orderbycolumn Orderby Column Name e.g. employeename
 * @param string $whereCol where column name e.g. employee_id, generally primary key selector
 * @param numeric $whereID where id to select a row
 * @param string $db Common database connection string parameter
 * @return string $rows Whole table as rows
 */
function dateBetween($tblname, $orderby, $betCol, $from, $to, $orderbycolumn, $whereCol, $whereID, $db) {
    $query = " SELECT * FROM ".$tblname." WHERE ".$whereCol." = ".$whereID." AND ".$betCol." BETWEEN '".$from."' AND '".$to."' ORDER BY ".$orderbycolumn." ".$orderby." ";
    
    //var_dump($query);
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    return $rows;
}

/**
 * 
 * @param string $tblname Table Name
 * @param numeric $limit the limit of the query
 * @param string $whereCol where column name e.g. employee_id, generally primary key selector
 * @param numeric $whereID where id to select a row
 * @param string $db Common database connection string parameter
 * @return string $rows Returning the rows of a table
 */
function selectAllWhereA($tblname, $limit, $whereCol, $whereID, $db) {
    $query = " SELECT * FROM ".$tblname." WHERE ".$whereCol." = ".$whereID." ".$limit." ";

    //var_dump($query);
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    return $rows;
}
/**
 *
 * @param string $tbl1 Table name one
 * @param string $tbl2 Table name one
 * @param string $mctbl1 Match column name of 1st table
 * @param string $mctbl2 Match column name of 2nd table
 * @param numeric $limit Page view rows from the total rows
 * @param string $whereCol Where column name
 * @param numeric $whereID Where ID
 * @param string $db
 * @return string $rows
 */
function selectAllMatchColumn($tbl1, $tbl2, $mctbl1, $mctbl2, $limit, $whereCol, $whereID, $db) {
    $query = "
        SELECT t1.*, t1.inqty, IFNULL(t2.outqty, '0') outqty, (IFNULL(t1.inqty,'0') - IFNULL(t2.outqty, '0')) totalHand FROM
            (
                SELECT *, SUM(in_quantity) inqty FROM stockin GROUP BY serialno
            ) t1 LEFT JOIN
            (
                SELECT serialno, SUM(out_quantity) outqty FROM stockout GROUP BY serialno
            ) t2
        ON t1.serialno = t2.serialno
        ";
    //$query = "SELECT * FROM ".$tbl1." as tbl1 WHERE NOT EXISTS ( SELECT 1 FROM ".$tbl2." as tbl2 WHERE tbl1.".$mctbl1." = tbl2.".$mctbl2." ) AND ".$whereCol." = ".$whereID." ".$limit."";
    //var_dump($query);
   
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    return $rows;
}


/**
 * 
 * @param type $tblname
 * @param type $limit
 * @param type $whereCol
 * @param type $whereID
 * @param type $db
 * @return type
 */
function selectAllWhereWithDateDifference($tblname, $limit, $whereCol, $whereID, $db) {
    $query = "SELECT A.*, (TIME_TO_SEC(TIMEDIFF(`receivedtime`,`deliverytime`)) < TIME_TO_SEC(`sla`)) as `checkSla` FROM stockout A WHERE ".$whereCol." = ".$whereID." ".$limit."";

    //var_dump($query);
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    return $rows;
}

/**
 * 
 * @param string $tblname Table Name
 * @param string $whereCol where column name e.g. employee_id, generally primary key selector
 * @param numeric $whereID where id to select a row
 * @param string $db Common database connection string parameter
 * @return string $rows Returning the rows of a table
 */
function selectAllWhereAA($tblname, $whereCol, $whereID, $db) {
    $query = ' SELECT * FROM '.$tblname.' WHERE '.$whereCol.' = "'.$whereID.'" ';
    //var_dump($query);
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    return $rows;
}

/**
 * 
 * @param string $tblname Table Name
 * @param string $orderby You can order data using Ascending/Descending, use ASC or DESC onto this parameter
 * @param string $orderbycolumn Orderby Column Name e.g. employeename
 * @param numeric $offset the offset of the limit of a query
 * @param numeric $limit the limit of the query
 * @param string $db Common database connection string parameter
 * @return string $rows Returning the rows of a table
 */
function selectAllWhereForPanel($tblname, $orderby, $orderbycolumn, $offset, $limit, $db) {
    $query = 'SELECT * FROM '.$tblname.' ORDER BY '.$orderbycolumn.' '.$orderby.' LIMIT '.$offset.', '.$limit.' ';
    //LIMIT '.$offset.', '.$limit.'
    //var_dump($query);

    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    return $rows;
}

/**
 * 
 * @param string $tblname Table Name
 * @param string $countcolname will count the total column number of a table
 * @param numeric $limit the limit of the query
 * @param string $whereCol where column name e.g. employee_id, generally primary key selector
 * @param numeric $whereID where id to select a row
 * @param string $db Common database connection string parameter
 * @return string $tPages Returning the total count of rows by deviding tRecords with limit
 */
function getCount($tblname, $countcolname, $limit, $whereCol, $whereID, $db) {
    $sql = 'SELECT COUNT('.$countcolname.') as total FROM '.$tblname.' WHERE '.$whereCol.' = "'.$whereID.'"';
    
    try {
        $stmt = $db->prepare($sql);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows1 = $stmt->fetchAll();

    $tRecords = $rows1[0]['total'];
    $tPages = ceil($tRecords / $limit);
    return $tPages;
}

/**
 * 
 * @param string $tblname Table Name
 * @param string $countcolname will count the total column number of a table
 * @param string $whereCol where column name e.g. employee_id, generally primary key selector
 * @param numeric $whereID where id to select a row
 * @param string $db Common database connection string parameter
 * @return string $rows1 Returning the rows of a table
 */
function getCountA($tblname, $countcolname, $whereCol, $whereID, $db) {
    $sql = 'SELECT COUNT('.$countcolname.') as total FROM '.$tblname.' WHERE '.$whereCol.' = "'.$whereID.'" ';
    
    try {
        $stmt = $db->prepare($sql);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows1 = $stmt->fetchAll();
    return $rows1;
}
function getCountMatchColumn($tbl1, $tbl2, $mctbl1, $mctbl2, $whereCol, $whereID, $db) {
    //$sql = 'SELECT COUNT('.$countcolname.') as total FROM '.$tblname.' WHERE '.$whereCol.' = "'.$whereID.'" ';
    $sql = "SELECT COUNT('$mctbl1') as total FROM ".$tbl1." as tbl1 WHERE NOT EXISTS ( SELECT 1 FROM ".$tbl2." as tbl2 WHERE tbl1.".$mctbl1." = tbl2.".$mctbl2." ) AND ".$whereCol." = ".$whereID."";
    //var_dump($sql);
    try {
        $stmt = $db->prepare($sql);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows1 = $stmt->fetchAll();
    return $rows1;
}

/**
 * 
 * @param string $tblname Table Name
 * @param string $countcolname will count the total column number of a table
 * @param numeric $limit the limit of the query
 * @param string $db Common database connection string parameter
 * @return string $tPages Returning the total count of pages.
 */
function getCountForPanel($tblname, $countcolname, $limit, $db) {
    $sql = 'SELECT COUNT('.$countcolname.') as total FROM '.$tblname.'';
    
    try {
        $stmt = $db->prepare($sql);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());;
    }
    $rows1 = $stmt->fetchAll();

    $tRecords = $rows1[0]['total'];
    $tPages = ceil($tRecords / $limit);
    return $tPages;
}

/**
 * 
 * @param string $tblname Table Name
 * @param string $selCol is used to select a specific column e.g. employee_name from a table and get the value of this column
 * @param string $whereCol where column name e.g. employee_id, generally primary key selector
 * @param numeric $whereID where id to select a row
 * @param string $db Common database connection string parameter
 * @return string $rows Returning the rows of a table
 */
function getName($tblname, $selCol, $whereCol, $whereID, $db) {
    $query = ' SELECT '.$selCol.' FROM '.$tblname.' WHERE '.$whereCol.' = '.$whereID.' LIMIT 0,1 ';
    
    //var_dump($query);
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    foreach($rows as $row) {
        $roow = $row[$selCol];
    }
    return $roow;
}

/**
 * 
 * @param numeric $id is used to get user type id from user table
 * @param string $db Common database connection string parameter
 * @return string $row1 Returning the u_type_name of a user type table
 */
function getUserType($id, $db){
	$query = "SELECT u_type_id, u_type_name FROM usertype WHERE u_type_id={$id} LIMIT 1";
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $row1 = $stmt->fetch();
    return $row1['u_type_name'];
}

/**
 * 
 * @param string $getValue 
 * @return string $cts Change to space
 * 
 */
function changeToSpace($getValue) {
    if($getValue == '666') { 
        $cts.="&nbsp;";
    } else { 
        $cts.=$getValue;
    }
    return $cts;
}

function theSummary($db) {
    $sql1 = "
        SELECT 
            t1.productid productid, 
            t1.description description, 
            t1.inqty InQty, 
            IFNULL(t2.outqty, '0') OutQty, 
            (IFNULL(t1.inqty, '0') - IFNULL(t2.outqty, '0')) Total 
            FROM
                (
                    SELECT 
                    productid, 
                    description, 
                    SUM(in_quantity) InQty 
                    FROM stockin GROUP BY productid
                ) t1
                LEFT JOIN
                (
                SELECT 
                    productid, 
                    SUM(out_quantity) outqty 
                    FROM stockout GROUP BY productid
                ) t2
        ON t1.productid = t2.productid
        ";
    try {
        $stmt = $db->prepare($sql1);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows1 = $stmt->fetchAll();
    return $rows1;
}
/**
 * 
 * @param int $userid User ID based on work
 * @param string $db Common Parameter
 * @return type $rows1
 */
function modifyStockout($userid, $db) {
    $sql1 = "SELECT * FROM stockout WHERE userid = {$userid} AND inserteddate BETWEEN DATE( DATE_SUB( NOW(), INTERVAL 2 DAY ) ) AND NOW()";
    //var_dump($sql1);
    try {
        $stmt = $db->prepare($sql1);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows1 = $stmt->fetchAll();
    return $rows1;
}
/**
 * 
 * @param int $id Select single by id
 * @param string $db common db string
 */
function selectSingle($id, $db) {
    $sql1 = "SELECT * FROM stockout WHERE stockout_id = {$id}";
    try {
        $stmt = $db->prepare($sql1);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $row = $stmt->fetchAll();
    return $row;
}
?>